﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using DBUtil;
using LIMS_DataSync;
using LIMS_DataSync.Entry;

namespace LIMS_DataSync
{
    public class SyncHelper
    {
        static string dbpath = AppDomain.CurrentDomain.BaseDirectory + "\\" + ("LIMS_DataSync.db");
        static SyncHelper()
        {
            string exist_dbtable = "select count(1) from sqlite_master where type='table' and name = 'SyncRecords'";
            if (!File.Exists(dbpath) || !db.Exists(exist_dbtable))
            {
                DBSession dbSqlite = new DBSession(DataBaseType.Sqlite, "Data Source=" + dbpath);
                string createDB_sql = @"CREATE TABLE SyncRecords
                    (
                      Id INTEGER PRIMARY KEY autoincrement,
                      TbName varchar(200) NOT null,
                      Mode INTEGER NOT null ,
                      TbMd varchar(300) NOT null unique,
                      SignColName varcahar(200) not null,
                      WhereTime varchar(4000) 
                    );";
                int res = dbSqlite.ExecuteSql(createDB_sql);
                LogUtils.LogInfo("初始化配置", Developer.MJ, "创建 SyncRecords 成功!", dbpath);
                InitConfig();
            }
            LogUtils.LogInfo("初始化配置", Developer.MJ, "读取到 SyncRecords 成功!", dbpath);
        }

        static DBSession db
        {
            get { return new DBSession(DataBaseType.Sqlite, "Data Source=" + dbpath); }
        }
        public static Dictionary<string, string> GetSyncTabNames(SyncMode mode)
        {
            string sel_sql = "select distinct TbName,WhereTime from SyncRecords where Mode=" + Convert.ToInt32(mode);
            return db.QueryTable(sel_sql).GetDict<string, string>("TbName", "WhereTime");
        }

        public static void SaveTimeSP(string tableName, SyncMode mode, object timeSP)
        {
            string tbMd = (tableName + "_" + mode.ToString()).ToLower();
            Hashtable ht = new Hashtable();
            ht.Add("TbMd", tbMd);
            SyncConfig syncCfg = GetConfig(mode);
            SyncParams para = syncCfg.Config.Find(t => t.SourceTbName.Equals(tableName));

            string sel_sql = "select SignColName from SyncRecords where TbMd='{0}'";
            sel_sql = string.Format(sel_sql, tbMd);
            string SignColName = db.GetSingle<string>(sel_sql);

            if (mode == SyncMode.上传) //SqlServer 上传 MySql 
            {
                string whereTsp = " and " + SignColName + " >  {0} ";
               // string tsp = Utils.ConvertTo<DateTime>(timeSP, DateTime.Parse("1970-01-01")).ToString("yyyy-MM-dd HH:mm:ss.fff");
                string lastMark = timeSP.ToString();
                if (timeSP is byte[])
                {
                    lastMark = TimeStampOper.ToTimeStamp((byte[])timeSP);
                }
                whereTsp = string.Format(whereTsp, lastMark);
                ht.Add("WhereTime", whereTsp);
            }
            else
            {
                string whereTsp = " and " + SignColName + " >  '{0}' ";
                string tsp = Utils.ConvertTo<DateTime>(timeSP, DateTime.Parse("1970-01-01")).ToString("yyyy-MM-dd HH:mm:ss");
                whereTsp = string.Format(whereTsp, tsp);
                ht.Add("WhereTime", whereTsp);
            }
            System.Threading.Thread.Sleep(500);//The database file is locked database is locked   防止sqlite3 繁忙写入不进去         
            db.Update(ht, "SyncRecords", "TbMd");
        }

        public static string GetWhereTime(string tableName, SyncMode mode)
        {
            string tbMd = (tableName + "_" + mode.ToString()).ToLower();
            string sel_sql = "select WhereTime from SyncRecords where tbMd='{0}'";
            sel_sql = string.Format(sel_sql, tbMd);
            DataRow dr = db.QueryRow(sel_sql);
            if (dr != null)
            {
                return dr["WhereTime"].ToString();
            }
            return string.Empty;
        }

        private static bool ExistTbMd(string tabName, SyncMode mode)
        {
            string exist_sql = "select count(1) from SyncRecords where TbMd='{0}'";
            string tbMd = (tabName + "_" + mode.ToString()).ToLower();
            exist_sql = string.Format(exist_sql, tbMd);
            return db.Exists(exist_sql);
        }



        /// <summary>
        /// 初始化程序配置
        /// </summary>
        private static void InitConfig()
        {
            string configFile = AppDomain.CurrentDomain.BaseDirectory + "\\" + "Sync.xml";
            if (File.Exists(configFile))
            {
                #region 读取配置文件
                string xmlContent = File.ReadAllText(configFile);
                bool isOk = false;
                if (!string.IsNullOrWhiteSpace(xmlContent))
                {
                    XElement xml = XElement.Parse(xmlContent.ToLower());

                    XElement xmlGlobal = xml.Element("globaldefconfig");
                    string g_SignColName = xmlGlobal.Element("signcolname").Value;

                    XElement xmlUpload = xml.Element("upload");
                    var elts_Upload = xmlUpload.Elements();
                    foreach (var elt in elts_Upload)
                    {
                        if (elt.Attribute("sourcetbname") == null
                            || string.IsNullOrWhiteSpace(elt.Attribute("sourcetbname").Value))
                        {
                            throw new ConfigurationErrorsException("SourceTbName节点配置出错！");
                        }

                        string SignColName = string.Empty;
                        if (elt.Attribute("signcolname") == null
                           || string.IsNullOrWhiteSpace(elt.Attribute("signcolname").Value))
                        {
                            SignColName = g_SignColName;
                        }
                        else
                        {
                            SignColName = elt.Attribute("signcolname").Value;
                        }

                        string tbName = elt.Attribute("sourcetbname").Value;
                        SyncMode mode = SyncMode.上传;
                        string TbMd = tbName + "_" + mode.ToString();

                        string tsp = " and  " + SignColName + " >  {0} ";
                        string whereTsp = string.Format(tsp, "0x0000000000000000");

                        Hashtable ht = new Hashtable();
                        ht.Add("TbName", tbName);
                        ht.Add("Mode", Convert.ToInt32(mode));
                        ht.Add("TbMd", TbMd);
                        ht.Add("SignColName", SignColName);
                        ht.Add("WhereTime", whereTsp);
                        isOk = db.Insert(ht, "SyncRecords", null);
                    }

                    XElement xmlDownload = xml.Element("download");
                    var elts_Download = xmlDownload.Elements();
                    foreach (var elt in elts_Download)
                    {
                        if (elt.Attribute("sourcetbname") == null
                            || string.IsNullOrWhiteSpace(elt.Attribute("sourcetbname").Value))
                        {
                            throw new ConfigurationErrorsException("SourceTbName节点配置出错！");
                        }

                        string SignColName = string.Empty;
                        if (elt.Attribute("signcolname") == null
                           || string.IsNullOrWhiteSpace(elt.Attribute("signcolname").Value))
                        {
                            SignColName = g_SignColName;
                        }
                        else
                        {
                            SignColName = elt.Attribute("signcolname").Value;
                        }

                        string tbName = elt.Attribute("sourcetbname").Value;
                        SyncMode mode = SyncMode.下发;
                        string TbMd = tbName + "_" + mode.ToString();

                        string tsp = " and " + SignColName + " >  '{0}' ";
                        string whereTsp = string.Format(tsp, DateTime.Parse("1970-01-01").ToString("yyyy-MM-dd HH:mm:ss"));

                        Hashtable ht = new Hashtable();
                        ht.Add("TbName", tbName);
                        ht.Add("Mode", Convert.ToInt32(mode));
                        ht.Add("TbMd", TbMd);
                        ht.Add("SignColName", SignColName);
                        ht.Add("WhereTime", whereTsp);
                        isOk = db.Insert(ht, "SyncRecords", null);
                    }
                }
                #endregion
            }
            LogUtils.LogInfo("初始化配置", Developer.MJ, "完成DataSync初始化配置！");
        }


        public static SyncConfig GetConfig(SyncMode mode)
        {
            string configFile = AppDomain.CurrentDomain.BaseDirectory + "\\" + "Sync.xml";
            if (File.Exists(configFile))
            {
                string xmlContent = File.ReadAllText(configFile);
                if (!string.IsNullOrWhiteSpace(xmlContent))
                {
                    SyncConfig syncfg = new SyncConfig();

                    XElement xml = XElement.Parse(xmlContent.ToLower());
                    XElement xmlGlobal = xml.Element("globaldefconfig");

                    string g_SelectColNames = xmlGlobal.Element("selectcolnames").Value;
                    string g_SignColName = xmlGlobal.Element("signcolname").Value;
                    string g_UniqueColName = xmlGlobal.Element("uniquecolname").Value;

                    XElement xmlPara = null;
                    if (mode == SyncMode.上传)
                    {
                        xmlPara = xml.Element("upload");
                    }
                    else if (mode == SyncMode.下发)
                    {
                        xmlPara = xml.Element("download");
                    }

                    if (xmlPara.Attribute("limittype") == null
                        || string.IsNullOrWhiteSpace(xmlPara.Attribute("limittype").Value))
                    {
                        throw new ConfigurationErrorsException("limitType节点配置出错！");
                    }

                    string limitType = xmlPara.Attribute("limittype").Value;
                    syncfg.LmtType = Utils.ConvertTo<LimitType>(limitType);


                    List<SyncParams> lstSP = new List<SyncParams>();
                    var elts = xmlPara.Elements();
                    foreach (var elt in elts)
                    {
                        SyncParams para = new SyncParams();


                        if (elt.Attribute("sourcetbname") == null
                            || string.IsNullOrWhiteSpace(elt.Attribute("sourcetbname").Value))
                        {
                            throw new ConfigurationErrorsException("SourceTbName节点配置出错！");
                        }
                        para.SourceTbName = elt.Attribute("sourcetbname").Value;


                        if (elt.Attribute("wheredef") != null
                           && !string.IsNullOrWhiteSpace(elt.Attribute("wheredef").Value))
                        {
                            para.WhereDef = elt.Attribute("wheredef").Value;
                        }


                        if (elt.Attribute("signcolname") == null
                            || string.IsNullOrWhiteSpace(elt.Attribute("signcolname").Value))
                        {
                            para.SignColName = g_SignColName;
                        }
                        else
                        {
                            para.SignColName = elt.Attribute("signcolname").Value;
                        }


                        if (elt.Attribute("selectcolnames") == null
                            || string.IsNullOrWhiteSpace(elt.Attribute("selectcolnames").Value))
                        {
                            para.SelectColNames = g_SelectColNames;
                        }
                        else
                        {
                            para.SelectColNames = elt.Attribute("selectcolnames").Value;
                        }


                        if (elt.Attribute("mappertbname") == null
                           || string.IsNullOrWhiteSpace(elt.Attribute("mappertbname").Value))
                        {
                            throw new ConfigurationErrorsException("MapperTbName节点配置出错！");
                        }
                        para.MapperTbName = elt.Attribute("mappertbname").Value;



                        if (elt.Attribute("uniquecolname") == null
                            || string.IsNullOrWhiteSpace(elt.Attribute("uniquecolname").Value))
                        {
                            para.UniqueColName = g_UniqueColName;
                        }
                        else
                        {
                            para.UniqueColName = elt.Attribute("uniquecolname").Value;
                        }


                        if (elt.Attribute("excludecolnames") != null
                            && !string.IsNullOrWhiteSpace(elt.Attribute("excludecolnames").Value))
                        {
                            para.ExcludeColNames = elt.Attribute("excludecolnames").Value;
                        }
                        else
                        {
                            para.ExcludeColNames = string.Empty;
                        }
                        lstSP.Add(para);
                    }
                    syncfg.Config = lstSP;

                    return syncfg;
                }
            }
            return null;
        }

    }






}
